{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "import pandas as pd\n",
    "from pandas import DataFrame, Series\n",
    "import re\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = r'D:\\桌面\\2017年 8月份员工工资表 .xls'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Sheets([<Sheet [2017年 8月份员工工资表 .xls]汇总>, <Sheet [2017年 8月份员工工资表 .xls]汇总 (2)>, <Sheet [2017年 8月份员工工资表 .xls]科室>, ...])"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "app = xw.App(visible=True, add_book=False)\n",
    "xb = app.books.open(path)\n",
    "xb.sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "huashiche = [i for i in xb.sheets if i.name == '花式车'][0]\n",
    "hua = DataFrame(huashiche.range('a3:i30').value[1:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>xiaoshi</th>\n",
       "      <th>tianshu</th>\n",
       "      <th>heji</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>307.000000</td>\n",
       "      <td>307.000000</td>\n",
       "      <td>3.070000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>316.448208</td>\n",
       "      <td>28.133550</td>\n",
       "      <td>-6.988864e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>259.374008</td>\n",
       "      <td>24.707242</td>\n",
       "      <td>1.225261e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>207.500000</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>-2.146826e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>283.500000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>3.234000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>312.500000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>3.880000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>318.000000</td>\n",
       "      <td>27.000000</td>\n",
       "      <td>4.578000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>4828.000000</td>\n",
       "      <td>459.000000</td>\n",
       "      <td>5.265200e+04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           xiaoshi     tianshu          heji\n",
       "count   307.000000  307.000000  3.070000e+02\n",
       "mean    316.448208   28.133550 -6.988864e+06\n",
       "std     259.374008   24.707242  1.225261e+08\n",
       "min     207.500000   20.000000 -2.146826e+09\n",
       "25%     283.500000   27.000000  3.234000e+03\n",
       "50%     312.500000   27.000000  3.880000e+03\n",
       "75%     318.000000   27.000000  4.578000e+03\n",
       "max    4828.000000  459.000000  5.265200e+04"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = []\n",
    "for i in xb.sheets:\n",
    "    if '组' in i.name:\n",
    "#         print(i)\n",
    "        sht = i.range('a4:i30').value\n",
    "        data.extend(sht)\n",
    "df = DataFrame(data)\n",
    "df = pd.concat([hua, df], axis=0)\n",
    "df.columns  =['xuhao', 'name', 'zhanghao', 'xiaoshi', 'tianshu', 'quanqin', 'gongzi', 'butie', 'heji']\n",
    "new_data = df[df['tianshu'] >= 20]\n",
    "# aa = new_data.where(new_data['name'].isnull() == False)\n",
    "new_data.reset_index(drop=True)\n",
    "new_data.head()\n",
    "new_data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(97149.6, 8637.0, -2145581341.0)"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "renshu = new_data['name'].count()\n",
    "sum_time = new_data['xiaoshi'].sum()\n",
    "sum_date = new_data['tianshu'].sum()\n",
    "sum_pay = new_data['heji'].sum()\n",
    "sum_time, sum_date, sum_pay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(316.44820846905537, 28.950819672131146, -6988864.3029315965)"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "average_pay = sum_pay/renshu\n",
    "average_time = sum_time/renshu\n",
    "# average_date = sum_date/renshu\n",
    "# average_pay\n",
    "# average_date\n",
    "average_time, average_date, average_pay"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
